package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.halm_lineEfficiency_report.HalmLineEfficiencyReportDataBean;
import com.navi.bean.out.halm_lotEfficiency_report.HalmLotEfficiencyReportDataBean;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.pojo.EfgrPojo;
import com.navi.mould1.pojo.ToolToolgLineFabPojo;
import com.navi.mould1.pojo.WaferSpecPojo;
import com.navi.mould1.service.IHalmLotEfficiencyReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;


@Slf4j
@Service
public class HalmLotEfficiencyReportServiceImpl implements IHalmLotEfficiencyReportService {


    public List<HalmLotEfficiencyReportDataBean> getGridData(ConditionBean conditions) {

        //优化查询速度，把Sql中子查询拿出来单独查
        List<String> tool_id_fk_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getToolg_id()) || StringUtils.isNotEmpty(conditions.getTool_type()) || StringUtils.isNotEmpty(conditions.getTool_id())) {
            SqlVo sql = new SqlVo("select tool_id as tool_id_fk from bis_tool where 1=1");
            if (StringUtils.isNotEmpty(conditions.getToolg_id())) sql.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + ")");
            if (StringUtils.isNotEmpty(conditions.getTool_type())) sql.appendSql("and unit_typ in" + jointInSql(conditions.getTool_type()));
            if (StringUtils.isNotEmpty(conditions.getTool_id())) sql.appendSql("and tool_id in" + jointInSql(conditions.getTool_id()));
            List<ToolToolgLineFabPojo> list = mySql_QueryRunner_QueryListBySql(sql, ToolToolgLineFabPojo.class);
            tool_id_fk_list.addAll(list.stream().map(data -> data.getTool_id_fk()).distinct().collect(Collectors.toList()));
        }
        List<String> wafer_spec_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getSpec()) || StringUtils.isNotEmpty(conditions.getWafer_spec())) {
            SqlVo sql = new SqlVo("select data_id as wafer_spec from bis_data where data_cate = 'WFSF'");
            if (StringUtils.isNotEmpty(conditions.getSpec())) sql.appendSql("and data_ext in" + jointInSql(conditions.getSpec()));
            if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sql.appendSql("and data_id in" + jointInSql(conditions.getWafer_spec()));
            List<WaferSpecPojo> list = mySql_QueryRunner_QueryListBySql(sql, WaferSpecPojo.class);
            wafer_spec_list.addAll(list.stream().map(data -> data.getWafer_spec()).distinct().collect(Collectors.toList()));
        }

        final SqlVo sqlVo = new SqlVo();
        sqlVo.appendSql("SELECT");
        sqlVo.appendSql("*");
        sqlVo.appendSql("FROM icim_jn.halm_test_data_bin_summary A");
        sqlVo.appendSql("WHERE 1=1");
        //if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND A.TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        //if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + "))");
        //if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where unit_typ in" + jointInSql(conditions.getTool_type()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
        if (CollectionUtils.isNotEmpty(tool_id_fk_list)) sqlVo.appendSql("AND A.TOOL_ID_FK in" + tool_id_fk_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        //if (StringUtils.isNotEmpty(conditions.getSpec())) sqlVo.appendSql("AND A.WAFER_SPEC in(select data_id from bis_data where data_cate = 'WFSF' and data_ext in" + jointInSql(conditions.getSpec()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sqlVo.appendSql("AND A.WAFER_SPEC in" + jointInSql(conditions.getWafer_spec()));
        if (CollectionUtils.isNotEmpty(wafer_spec_list)) sqlVo.appendSql("AND A.WAFER_SPEC in" + wafer_spec_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        if (StringUtils.isNotEmpty(conditions.getManufacturer())) sqlVo.appendSql("AND A.CUS_ID_FK in" + jointInSql(conditions.getManufacturer()));
        if (StringUtils.isNotEmpty(conditions.getBase_id())) sqlVo.appendSql("AND A.BASE_ID in" + jointInSql(conditions.getBase_id()));
        //if (StringUtils.isNotEmpty(conditions.getAttributes())) sqlVo.appendSql("AND A.ATTRIBUTES in" + jointInSql(conditions.getAttributes()));
        //if (StringUtils.isNotEmpty(conditions.getGear())) sqlVo.appendSql("AND A.GEAR in" + jointInSql(conditions.getGear()));
        if (StringUtils.isNotEmpty(conditions.getLot_id())) sqlVo.appendSql("AND A.LOT_ID in" + jointInSql(conditions.getLot_id()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') >= ?", conditions.getStart_time()+":00:00");
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') < ?", conditions.getEnd_time()+":00:00");

        //return mySql_QueryRunner_QueryListBySql(sqlVo, HalmLotEfficiencyReportDataBean.class);
        List<HalmLotEfficiencyReportDataBean> gridOutList = mySql_QueryRunner_QueryListBySql(sqlVo, HalmLotEfficiencyReportDataBean.class);

        if (CollectionUtils.isNotEmpty(gridOutList) && StringUtils.isNotEmpty(conditions.getEff_no())) {
            SqlVo sql = new SqlVo("select data_id as attributes, ext_1 as efficiency, ext_2 as bin from bis_data where data_cate = 'EFGR' and data_ext in" + jointInSql(conditions.getEff_no()));
            List<EfgrPojo> efgr_list = mySql_QueryRunner_QueryListBySql(sql, EfgrPojo.class);
            gridOutList.forEach(data -> {
                data.setGear(efgr_list.stream()
                        .filter(efgrPojo -> StringUtils.isNotEmpty(data.getBin()) && data.getBin().equals(efgrPojo.getBin()))
                        .filter(efgrPojo -> StringUtils.isNotEmpty(data.getAttributes()) && data.getAttributes().equals(efgrPojo.getAttributes()))
                        .map(efgrPojo -> efgrPojo.getEfficiency())
                        .findFirst()
                        .orElse("")
                );
            });
        }
        gridOutList = gridOutList.stream().filter(data -> StringUtils.isEmpty(conditions.getGear()) ? true : Arrays.asList(conditions.getGear().split(",")).contains(data.getGear())).collect(Collectors.toList());

        return gridOutList;
    }




}
